/**
 * www.ewanse.com.
 * Copyright (c) 2009-2016 All Rights Reserved.
 */
package com.sunday.utils;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.ConvertUtils;
import org.apache.commons.beanutils.PropertyUtils;

import com.alibaba.fastjson.JSON;
import com.google.common.base.CaseFormat;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;

public class JDBCUtil {
    private static String driver   = "com.mysql.jdbc.Driver";
    private static String url      = "jdbc:mysql://112.124.52.188:3306";
    private static String user     = "root";
    private static String password = "1wL5S4rMtPxVa37jp";

    public static void main(String[] args) throws InstantiationException, IllegalAccessException,
                                          InvocationTargetException, NoSuchMethodException, NoSuchFieldException,
                                          SecurityException {
        String sql = "select cat_id from " + "gzseed_vancelle_goods.gss_goods where goods_id= '167' ";
        List<GoodsVO> goodsList = excute(sql, GoodsVO.class);
        System.out.println(JSON.toJSONString(goodsList));
        //        List<Map<String, String>> list = excute(sql);
        //        System.out.println(JSON.toJSONString(list));
        //        Map<String, String> list = Maps.newHashMap();
        //        String str = MoreObjects.toStringHelper(list).addValue(new Date()).addValue("w34").toString();
        //        System.out.println(str);
        //        AbstractConverter convert = new DateConverter();
        //        String obj = DateUtil.format(new Date(), DateUtil.DEFAULT_FORMT);
        //        Date date = (Date) convert.convert(Date.class, obj);
        //        System.out.println(DateUtil.format(date, DateUtil.DEFAULT_FORMT));
    }

    public static <T> List<T> excute(String sql, Class<T> clazz) throws InstantiationException, IllegalAccessException,
                                                                InvocationTargetException, NoSuchMethodException,
                                                                NoSuchFieldException, SecurityException {

        //        List<String> fieldNames = BeanUtil.getPropertyNames(clazz);
        List<Map<String, String>> rows = excute(sql);
        List<T> objects = Lists.newArrayListWithCapacity(rows.size());
        for (Map<String, String> rowMap : rows) {
            T obj = clazz.newInstance();
            for (String filedName : rowMap.keySet()) {
                Field field = clazz.getDeclaredField(filedName);
                Class fieldType = field.getType();
                Object value = ConvertUtils.convert(rowMap.get(filedName), fieldType);
                PropertyUtils.setProperty(obj, filedName, value);
            }
            objects.add(obj);
        }

        return objects;
    }

    public static List<Map<String, String>> excute(String sql) {
        ResultSet rs = execute(sql);
        return handelResult(rs);
    }

    public static List<Map<String, String>> handelResult(ResultSet rs) {
        List<Map<String, String>> result = Lists.newArrayList();
        try {
            ResultSetMetaData rsmt = rs.getMetaData();
            int columnCount = rsmt.getColumnCount();
            Map<String, String> columnNames = Maps.newHashMap();
            for (int i = 1; i <= columnCount; i++) {
                String columnName = rsmt.getColumnName(i);
                columnNames.put(columnName, CaseFormat.LOWER_UNDERSCORE.to(CaseFormat.LOWER_CAMEL, columnName));
            }
            while (rs.next()) {
                Map<String, String> lineMap = Maps.newHashMap();
                for (String columnName : columnNames.keySet()) {
                    int columnValue = rs.getInt(columnName);
                    //                                        columnValue = rs.wasNull() ? null : rs.getString(columnName);
                    System.out.println(columnValue);
                    //                    lineMap.put(columnNames.get(columnName), columnValue);
                }
                result.add(lineMap);
            }
        } catch (SQLException e) {
            System.out.println(e);
        }
        return result;
    }

    /**
     * 执行sql语句
     * 
     * @param sql
     * @param 执行结果
     */
    public static ResultSet execute(String sql) {
        Connection conn = getConnection();
        Statement statement;
        try {
            statement = conn.createStatement();
            return statement.executeQuery(sql);
        } catch (SQLException e) {
            System.out.println(e);
            return null;
        }

    }

    /**
     * 获取数据库连接
     * 
     * @return
     */
    public static Connection getConnection() {
        try {
            Class.forName(driver);
            Connection conn = DriverManager.getConnection(url, user, password);
            return conn;
        } catch (Exception e) {
            e.printStackTrace();

        }
        return null;
    }
}
